Amazon Redshiftの文字列処理における「大文字と小文字を区別しない照合」を試してみた
Amazon Redshift(以下、Redshift)で、文字列処理における「大文字と小文字を区別しない照合」がサポートされていたので、簡単な動作を確認してみました。
はじめに
文字列処理における「大文字と小文字を区別する/しない」とはどういうことか、下記を確認していただくとイメージしやすいかと思います。
大文字と小文字を区別する場合
select * from collation_test; id | name ----+------- 1 | apple 2 | apPLE 3 | APPLE (3 rows) select * from collation_test where name = 'apple'; id | name ----+------- 1 | apple (1 row) select * from collation_test where name = 'APPLE'; id | name ----+------- 3 | APPLE (1 row)
上記のように大文字と小文字を区別する(別の値とみなす)ので、小文字の値で検索した場合は小文字の値のみが検索対象となり、大文字の値で検索した場合は大文字の値のみが対象となります。
大文字と小文字を区別しない場合
select * from collation_test2; id | name ----+------- 1 | apple 2 | apPLE 3 | APPLE (3 rows) select * from collation_test2 where name = 'apple'; id | name ----+------- 1 | apple 2 | apPLE 3 | APPLE (3 rows) select * from collation_test2 where name = 'APPLE'; id | name ----+------- 1 | apple 2 | apPLE 3 | APPLE (3 rows)
上記のように大文字も小文字を区別しない(同じ値とみなす)ので、小文字の値で検索をしても大文字の値も対象となります。逆に大文字の値で検索しても、小文字の値も検索対象となります。
これまでは、文字列処理における大文字と小文字を区別する(別の値とみなす)照合が設定されていたのですが、 「大文字と小文字を区別しない照合」をデータベースやテーブル作成時にオプションとして指定できるようになっていたので、本ブログにて確認してみます。
少し似ている内容になるのですが、データベース・テーブル・列の名前などの識別子について、大文字と小文字を区別する方法は下記のブログをご確認ください。
試してみた
環境
- Redshift クラスターバージョン：
1.0.28422 - クライアント：
psql (PostgreSQL) 12.4
データベース作成時に「大文字と小文字を区別する/しない」を指定する
新しいデータベースを作成するときに、「大文字と小文字を区別する/しない」をデータベースレベルのデフォルトの照合として指定することが可能です。
COLLATE CASE_SENSITIVE: 大文字と小文字を区別するCOLLATE CASE_INSENSITIVE: 大文字と小文字を区別しない
CREATE DATABASE database_name [ WITH ] [ COLLATE CASE_SENSITIVE | COLLATE CASE_INSENSITIVE ]
現時点でのデータベースレベルでの照合を確認したい場合は、下記のクエリで確認することができます。
select db_collation(); db_collation ------------------ case_insensitive (1 row)
照合を変更する場合は、下記の構文を使用します。
ALTER DATABASE database_name COLLATE CASE_SENSITIVE | COLLATE CASE_INSENSITIVE
データベースレベルの照合を変更する場合は、データベースが空の(テーブルが存在しない)場合しか使用できません。
データベースが空でない場合に、変更しようとすると下記のエラーとなります。
alter database test01 collate case_insensitive; ERROR: Cannot change case sensitivity: User created table found in the current database.
試しにcase_insensitive(大文字と小文字を区別しない)を設定したデータベースで、任意のテーブルを作成して確認してみます。
create table cm_ohama.tbl_1 ( col_1 BIGINT ENCODE AZ64, col_2 VARCHAR(20) ENCODE ZSTD, col_3 CHAR(10) ENCODE ZSTD );
VARCHAR列とCHAR列が対象となります。
select table_name, column_name, data_type, collation_name from svv_columns where table_schema = 'cm_ohama' and table_name = 'tbl_1' order by 2; table_name | column_name | data_type | collation_name ------------+-------------+-------------------+------------------ tbl_1 | col_1 | bigint | tbl_1 | col_2 | character varying | case_insensitive tbl_1 | col_3 | character | case_insensitive (3 rows)
col_2(VARCHAR列) と col_3(CHAR列) のcollation_nameの値がcase_insensitive(大文字と小文字を区別しない)となっていることが確認できました。
テーブル作成時に「大文字と小文字を区別する/しない」を指定する
新しいテーブルを作成するときに、VARCHAR列とCHAR列の照合を指定して、データベースレベルの照合を上書きすることができます。
試しにデータベース作成時はcase_sensitive(大文字と小文字を区別する)、テーブル作成時に任意のカラムにcase_insensitive(大文字と小文字を区別しない)を指定して確認してみます。
データベース作成の際に、COLLATE未指定で作成します。(未指定の場合は、case_sensitiveが適用されます。)
CREATE DATABASE test02;
select db_collation(); db_collation ---------------- case_sensitive (1 row)
col_2のみ、case_insensitive(大文字と小文字を区別しない)を指定して、テーブルを作成します。
create table cm_ohama.tbl_2 ( col_1 BIGINT ENCODE AZ64, col_2 VARCHAR(20) COLLATE CASE_INSENSITIVE ENCODE ZSTD, col_3 CHAR(10) ENCODE ZSTD );
col_2のみ、データベースレベルの照合(case_sensitive)を上書きして、case_insensitive(大文字と小文字を区別しない)となっていることが確認できました。
select table_name, column_name, data_type, collation_name from svv_columns where table_schema = 'cm_ohama' and table_name = 'tbl_2' order by 2; table_name | column_name | data_type | collation_name ------------+-------------+-------------------+------------------ tbl_2 | col_1 | bigint | tbl_2 | col_2 | character varying | case_insensitive tbl_2 | col_3 | character | case_sensitive (3 rows)
一応、データも投入して確認してみます。
insert into cm_ohama.tbl_2 values(1,'redshift','dwh'); insert into cm_ohama.tbl_2 values(2,'Redshift','dWh'); insert into cm_ohama.tbl_2 values(3,'REDSHIFT','DWH');
select * from cm_ohama.tbl_2 order by col_1;
col_1 | col_2 | col_3
-------+----------+------------
1 | redshift | dwh
2 | Redshift | dWh
3 | REDSHIFT | DWH
(3 rows)
上記のデータを踏まえて、はじめにcol_2を検索条件に指定して、小文字の値で検索してみます。
select * from cm_ohama.tbl_2 where col_2 = 'redshift' order by col_1;
col_1 | col_2 | col_3
-------+----------+------------
1 | redshift | dwh
2 | Redshift | dWh
3 | REDSHIFT | DWH
(3 rows)
col_2 は、case_insensitive(大文字と小文字を区別しない)なので、大文字のデータも対象となりました。
テーブル作成時のカラムごとに指定した照合(case_insensitive)がデータベース作成時の照合(case_sensitive)を上書きして、想定していたデータを抽出できています。
次に、col_3を検索条件に指定して、大文字の値で検索してみます。
select * from cm_ohama.tbl_2 where col_3 = 'DWH' order by col_1;
col_1 | col_2 | col_3
-------+----------+------------
3 | REDSHIFT | DWH
(1 row)
col_3 は、データベース作成時に指定されていた照合(case_sensitive)が適用されたままのため、大文字のデータのみ対象となりました。
COLLATE関数を用いて「大文字と小文字を区別する/しない」を指定する
SQLクエリでCOLLATE関数を用いて、VARCHAR列またはCHAR列の照合を指定することが可能です。 またデータベース作成時、テーブル作成時で指定した照合を上書きします。
select db_collation();
db_collation
----------------
case_sensitive
(1 row)
select table_name, column_name, data_type, collation_name from svv_columns where table_schema = 'cm_ohama' and table_name = 'tbl_2' order by 2;
table_name | column_name | data_type | collation_name
------------+-------------+-------------------+------------------
tbl_2 | col_1 | bigint |
tbl_2 | col_2 | character varying | case_insensitive
tbl_2 | col_3 | character | case_sensitive
(3 rows)
select * from cm_ohama.tbl_2 order by col_1;
col_1 | col_2 | col_3
-------+----------+------------
1 | redshift | dwh
2 | Redshift | dWh
3 | REDSHIFT | DWH
(3 rows)
上記のデータを踏まえて、COLLATE関数にて「大文字と小文字を区別しない照合」が上書きされていることを確認します。
select * from cm_ohama.tbl_2 where collate(col_3, 'case_insensitive') = 'DWH' order by col_1;
col_1 | col_2 | col_3
-------+----------+------------
1 | redshift | dwh
2 | Redshift | dWh
3 | REDSHIFT | DWH
(3 rows)
select table_name, column_name, data_type, collation_name from svv_columns where table_schema = 'cm_ohama' and table_name = 'tbl_2' order by 2;
table_name | column_name | data_type | collation_name
------------+-------------+-------------------+------------------
tbl_2 | col_1 | bigint |
tbl_2 | col_2 | character varying | case_insensitive
tbl_2 | col_3 | character | case_sensitive
(3 rows)
col_3 のcollation_nameがcase_sensitive(大文字と小文字を区別する)ではありますが、COLLATE関数を用いてcase_insensitive(大文字と小文字を区別しない)でデータを抽出できました。
ちなみに(ILIKE演算子について)
対象のデータベース、列の照合がcase_sensitive(大文字と小文字を区別する)の場合でも、ILIKE演算子を用いて、大文字と小文字を区別しないパターン一致を確認することはできます。
select db_collation();
db_collation
----------------
case_sensitive
(1 row)
select table_name, column_name, data_type, collation_name from svv_columns where table_schema = 'cm_ohama' and table_name = 'tbl_2' order by 2;
table_name | column_name | data_type | collation_name
------------+-------------+-------------------+------------------
tbl_2 | col_1 | bigint |
tbl_2 | col_2 | character varying | case_insensitive
tbl_2 | col_3 | character | case_sensitive
(3 rows)
select * from cm_ohama.tbl_2 where col_3 ilike '%dWh%';
col_1 | col_2 | col_3
-------+----------+------------
1 | redshift | dwh
3 | REDSHIFT | DWH
2 | Redshift | dWh
(3 rows)
注意事項
下記の注意事項などがあります。さらに詳しい内容については、データベースの作成などをご確認ください。
- PGカタログテーブルとAmazonRedshiftシステムテーブルを含むすべてのシステムテーブルまたはビューでは、大文字と小文字は区別される
- Amazon Redshift SpectrumやAmazon Aurora PostgreSQLフェデレーションクエリなどの外部クエリの場合、VARCHAR列およびCHAR列の照合は現在のデータベースレベルの照合が適用される
- 大文字と小文字を区別しないデータベースでのSUPER列は使用できない(※テーブル作成時に、
case_sensitive指定でも使用できない)SELECT db_collation(); db_collation ------------------ case_insensitive (1 row) create table cm_ohama.tbl_4 (col_1 super); ERROR: SUPER column is not supported in case insensitive database. create table cm_ohama.tbl_4 (col_1 super collate case_sensitive); ERROR: SUPER column is not supported in case insensitive database.
おわりに
簡単ではありますが、Redshiftの文字列処理における「大文字と小文字を区別しない照合」の設定および上書きの方法などについて確認しました。 「大文字と小文字を区別する/しない」は、各DBや各DWHによって仕様の違いがあると思うので、Redshiftへのデータ移行の際は改めて注意した上で活用していきたいと思います。
以上、DA(データアナリティクス)事業本部のナガマサでした。
![[アップデート] Amazon Redshiftのマルチデータウェアハウス書き込み機能が一般提供開始されたので試してみた](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-63f1274931b942e9a92e601c1127ad73/cfe87ec6d62fa2fc3c474ed4cb2f6c2e/amazon-redshift)
